<?php
if(!isset($_SESSION)){session_start();};
require_once("core.php");
include('includes/array.in.php');

$db->connectdb(DB_NAME,DB_USERNAME,DB_PASSWORD);

if($_POST['login']){
	$username	= $_POST['u'];
	$password	= $_POST['p'];

	$sql = 'SELECT * FROM members WHERE username = "'.$username.'" AND password = "'.md5($password).'"';
	$q = $db->select_query($sql);
	$rs= $db->fetch($q);
	$c = $db->rows($q);
	if($c > 0){
		$_SESSION['login']	= true;
		$_SESSION['user']	= $rs['username'];
		$_SESSION['uid']    = $rs['id'];
		echo 1;
	}else{
		echo 0;
	}
}

if($_POST['add_products']){
	$name 	= $_POST['data'][0]['value'];
	$cost	= $_POST['data'][1]['value'];
	$price	= $_POST['data'][2]['value'];
	$unit   = $_POST['data'][3]['value'];
	$num    = $_POST['data'][4]['value'];
	$promotion = $_POST['data'][5]['value'];
	$annotation= $_POST['data'][6]['value'];
	$code	= $_POST['data'][7]['value'];
	//print_r($_POST['data']);
	$sql = 'INSERT INTO products(code,name,price,unit_id,num,promotion,annotation,cost) VALUES("'.$code.'","'.$name.'","'.$price.'","'.$unit.'","'.$num.'","'.$promotion.'", "'.$annotation.'",'.$cost.')';
	$db->select_query($sql);
}

if($_POST['del_product']){
	$db->select_query('DELETE FROM products WHERE code = "'.$_POST['code'].'"');
}

if($_POST['delete']){
    $table_name = $_POST['table_name'];
    $id         = $_POST['id'];

    $sql = 'DELETE FROM '.$table_name.' WHERE id ='.$id;
    $q = mysql_query($sql)or die(mysql_error());

    if($q){
        echo 1;
    }else{
        echo 0;
    }
}

if($_GET['editable']){
    $table_name = $_REQUEST['table'];
    $id         = $_REQUEST['elementid'];
    $value      = $_REQUEST['newvalue'];
	$field		= $_REQUEST['field'];
    if($table_name == "products"){
        if(mysql_query('UPDATE '.$table_name.' SET '.$field.' = "'.$value.'" WHERE id = '.$id)){
            echo $value;
        }
    }
}

if($_GET['order']){
    $order_id 	= genStr();
	$bill_no	= date("dmYHms",time());
    foreach($_GET['products'] as $key => $data){
        $sql = 'INSERT INTO orders(order_id, product_code, num, unit, price)
                VALUES("'.$order_id.'","'.$data['code'].'",0,"'.$data['unit'].'",'.$data['price'].')';
        mysql_query($sql)or die(mysql_error());
    }
    mysql_query('INSERT INTO bills(bill_no,order_id,members_id) VALUES("'.$bill_no.'","'.$order_id.'",'.$_SESSION['uid'].')')or die(mysql_error());
    echo $bill_no;
}

if($_POST['update_bill']){
	$bill_no= $_POST['bill_no'];
	$field 	= $_POST['field'];
	$value	= $_POST['value'];
	
	$sql = 'UPDATE bills SET '.$field.'="'.$value.'" WHERE bill_no ="'.$bill_no.'"';
	if(mysql_query($sql)){
		echo 'Update bills success';
	}else{ 
		mysql_query($sql)or die(mysql_error()); 
	}
}

if($_POST['update_order']){
	$order_no = $_POST['order_no'];
	$code	= $_POST['code'];
	$field	= $_POST['field'];
	$value	= $_POST['value'];
	$sql = 'UPDATE orders SET '.$field.'="'.$value.'" WHERE product_code ="'.$code.'" AND order_id = "'.$order_no.'"';
	if(mysql_query($sql)){
		$pSql 	= 'SELECT num FROM products WHERE code = "'.$code.'"';
		$pQ		= mysql_query($pSql)or die(mysql_error());
		$pRs	= mysql_fetch_assoc($pQ);
		$updateNum = ($pRs['num'] > 0)? $pRs['num']-$value : 0 ;
		if(mysql_query('UPDATE products SET num = '.$updateNum.' WHERE code = "'.$code.'"')){
			echo 'Update order success';
		}
	}else{ 
		mysql_query($sql)or die(mysql_error()); 
	}
}

if($_GET['delete_selected']){
	$_GET['selected'] = str_replace('undefined','',$_GET['selected']);
	$_GET['table'] = str_replace('undefined','',$_GET['table']);
	//echo $table = $_GET['table'];
	foreach($_GET['selected'] as $value){
		if($_GET['table'] == "stickers"){
			$q = mysql_query('SELECT * FROM stickers WHERE id = '.$value)or die(mysql_error());
			$r = mysql_fetch_assoc($q);
			if(is_file('stickers/'.$r['img']) && unlink('stickers/'.$r['img'])){
				mysql_query('DELETE FROM '.$_GET['table'].' WHERE id = '.$value)or die(mysql_error());	
			}elseif(unlink('uploads/'.$r['file_name'])){
				mysql_query('DELETE FROM '.$_GET['table'].' WHERE id = '.$value)or die(mysql_error());	
			}
		}elseif($_GET['table'] == "bills"){
			mysql_query('DELETE FROM '.$_GET['table'].' WHERE id ='.$value)or die(mysql_error());
		}elseif($_GET['table'] == "products"){
			mysql_query('DELETE FROM '.$_GET['table'].' WHERE code = "'.$value.'"')or die(mysql_error());
		}
	}
}

if($_POST['update_item']){
	$billNo = $_POST['bill_no'];
	$sql = 'SELECT order_id FROM bills WHERE bill_no = "'.$billNo.'"';
	$billq = mysql_query($sql)or die(mysql_error());
	$orderid = mysql_fetch_assoc($billq);
	$orderq = mysql_query('SELECT product_code,num FROM orders WHERE order_id="'.$orderid['order_id'].'"')or die(mysql_error());
	while($order = mysql_fetch_assoc($orderq)){
		$getp = mysql_query('SELECT num FROM products WHERE code = "'.$order['product_code'].'"')or die(mysql_error());
		$product = mysql_fetch_assoc($getp);
		$update_product = 'UPDATE products SET num = '.($product['num']-$order['num']).' WHERE code = "'.$order['product_code'].'"';
		mysql_query($update_product)or die(mysql_error());
	}	
	echo 'success';
}

if($_POST['go_print']){
	$val 	= $_POST['value'];
	$img	= $_SESSION['img'];
	$sql 	= 'INSERT INTO stickers(img,txt) VALUES("'.$img.'","'.$val.'")';
	if(mysql_query($sql)){
		unset($_SESSION['img']);
		echo mysql_insert_id();
	}else{
		echo mysql_query($sql)or die(mysql_error());
	}
}

if($_POST['update_print']){
	$val 	= $_POST['value'];
	$img	= $_SESSION['img'];
	$id		= $_POST['id'];
	$sql 	= 'UPDATE stickers SET img="'.$img.'", txt="'.$val.'" WHERE id='.$id;
	if(mysql_query($sql)){
		unset($_SESSION['img']);
	}else{
		echo mysql_query($sql)or die(mysql_error());
	}
}

if($_POST['update_product']){
	$sql = 'UPDATE products SET ';
	$first = false;
	foreach($_POST['data'] as $key => $value){
		if($first){
			$sql .= ','.$value['name'].'="'.$value['value'].'" ';
		}else{
			$sql .= $value['name'].'="'.$value['value'].'" ';
			$first = true;
		}
	}	
	$sql .= ' WHERE code = "'.$_POST['code'].'"';
	if(mysql_query($sql)){
		unset($_SESSION['img']);
	}else{
		echo mysql_query($sql)or die(mysql_error());
	}
}

if($_POST['delete_sticker']){
	$id = $_POST['id'];
	$sql = 'DELETE FROM stickers WHERE id='.$id;
	$del = 'SELECT * FROM stickers WHERE id='.$id;
	$q = mysql_query($del)or die(mysql_error());
	if($rs = mysql_fetch_assoc($q)){
		if(is_file('stickers/'.$rs['img']) && unlink('stickers/'.$rs['img'])){
			mysql_query($sql)or die(mysql_error());		
		}elseif(unlink($rs['file_path'])){
			mysql_query($sql)or die(mysql_error());
		}else{
			unlink('uploads/'.$rs['file_name']);
			echo mysql_query($sql)or die(mysql_error());
		}
	}
}

if($_POST['searcher']){
	$bill_no = $_POST['bill'];
	$value = explode("โทร", $_POST['data']);
	$sql_name = 'SELECT * FROM customers WHERE name = "'.$value[0].'"';
	$sql_store= 'SELECT * FROM customers WHERE store_name = "'.$value[0].'"';
	$name_q = mysql_query($sql_name)or die(mysql_error());
	$count = mysql_num_rows($name_q);
	if($count != "" && $count > 0){
		$rs = mysql_fetch_assoc($name_q);
		$address = $rs['address'].' '.$rs['tumbon'].' '.$rs['amphut'].' '.$rs['province'].' '.$rs['postcode'];
		mysql_query('UPDATE bills SET customer_address = "'.$address.'" WHERE bill_no="'.$bill_no.'"');
		echo $address;
	}else{
		$store_q = mysql_query($sql_store)or die(mysql_error());
		$count = mysql_num_rows($store_q);
		if($count != "" && $count > 0){
			$rs = mysql_fetch_assoc($store_q);
			$address = $rs['address'].' '.$rs['tumbon'].' '.$rs['amphut'].' '.$rs['province'].' '.$rs['postcode'];
			mysql_query('UPDATE bills SET customer_address = "'.$address.'" WHERE bill_no="'.$bill_no.'"');
			echo $address;
		}
	}
}

if($_POST['search_products']){
	$bill_no 	= $_POST['bill'];
	$value		= $_POST['data'];
	$order_no	= $_POST['order_id'];
	$sql = 'SELECT
				`units`.`name` AS unit
				, `products`.*
			FROM
				`hkpacth_store`.`units`
			INNER JOIN `hkpacth_store`.`products` 
				ON (`units`.`id` = `products`.`unit_id`)
			WHERE `products`.`name` = "'.$value.'"';
	$q = mysql_query($sql)or die(mysql_error());
	$rs = mysql_fetch_assoc($q);
	mysql_query('INSERT INTO orders(order_id,product_code,num,unit,price) 
				 VALUES("'.$order_no.'","'.$rs['code'].'",0,"'.$rs['unit'].'",'.$rs['price'].')');
	echo '{"unit":"'.$rs['unit'].'", "price":"'.$rs['price'].'", "num":"'.$rs['num'].'", "code":"'.$rs['code'].'"}';
}

if($_POST['delete_products']){
	$product_code = $_POST['pid'];
	mysql_query('DELETE FROM orders WHERE product_code = "'.$product_code.'"')or die(mysql_error());
}

if($_POST['search_stock_products']){
	$product_name	= $_POST['data'];
	$total = 0;
	
	$sql = 'SELECT * FROM products WHERE name = "'.$product_name.'"';
	$pq = mysql_query($sql)or die(mysql_error());
	$rs = mysql_fetch_assoc($pq);
	
	$find_stock = 'SELECT total FROM stock WHERE product_name = "'.$product_name.'" ORDER BY id LIMIT 0,1';
	$sq = mysql_query($find_stock);
	$has_stock = mysql_num_rows($sq);
	if($has_stock > 0){
		$rs['num'] = 0;
		$srs = mysql_fetch_assoc($sq);
		$total = $srs['total'];
	}
	
	echo $sSql = 'INSERT INTO stock(stock_id,product_name,price,unit,bring,receive,sum,sale,bonus,all_sale,total,stock_date,annotation,cost) 
			VALUES("'.date('dmYHms',time()).'","'.$product_name.'",'.$rs['price'].','.$rs['unit_id'].','.$total.','.$rs['num'].','.($total+$rs['num']).',0,0,0,0,"'.date('dmY',time()).'","",'.$rs['cost'].')';
	$sin = mysql_query($sSql)or die(mysql_error());
}	

if($_POST['delete_stock']){
	mysql_query('DELETE FROM stockt WHERE stock_id = "'.$_POST['sid'].'"')or die(mysql_error());
}

if($_POST['update_sum_stock']){
	echo $sql = 'UPDATE stock SET receive='.$_POST['receive'].', sum='.$_POST['sum'].' WHERE stock_id="'.$_POST['stock_id'].'"';
	mysql_query($sql)or die(mysql_error());
}

if($_POST['update_stock']){
	$stock_id = $_POST['stock_id'];
	$bring	= (int)$_POST['bring'];
	$receive= (int)$_POST['receive'];
	$sum	= (int)$_POST['sum'];
	$sale	= (int)$_POST['sale'];
	$bonus	= (int)$_POST['bonus'];
	$all_sale= (int)$_POST['all_sale'];
	$total	= (int)$_POST['total'];
	
	$q = mysql_query('SELECT num FROM products WHERE name = "'.$_POST['pname'].'"')or die(mysql_error());
	$num = mysql_fetch_assoc($q);
	mysql_query('UPDATE products SET num = "'.(($num['num']+$receive)-$bonus).'" WHERE name = "'.$_POST['pname'].'"')or die(mysql_error());
	
	$sql = 'UPDATE stock SET bring='.$bring.', receive='.$receive.', sum='.$sum.', sale='.$sale.', bonus='.$bonus.', all_sale='.$all_sale.', total='.$total.' WHERE stock_id="'.$stock_id.'"';
	
	if(mysql_query($sql)){
		echo '{"status":1}';
	}else{
		echo '{"status":0}';
	}
	
}

if($_POST['update_annotation']){
	$annotation = $_POST['value'];
	$stock_id 	= $_POST['stock_id'];
	mysql_query('UPDATE stock SET annotation = "'.$annotation.'" WHERE stock_id = "'.$stock_id.'"')or die(mysql_error());
}

if($_POST['stock_report']){
	
	$date = $_POST['date'];
	$y = substr($date,strrpos($date,'/')+1);
	$d = str_replace($y,((int)$y-543),$date);
	$datetime = strtotime($d);
	
	$sql = 'SELECT
				`units`.`name`
				, `stock`.*
			FROM
				`hkpacth_store`.`units`
				INNER JOIN `hkpacth_store`.`stock` 
					ON (`units`.`id` = `stock`.`unit`) 
			WHERE stock_date = "'.date('dmY',$datetime).'"';
	$q = mysql_query($sql)or die(mysql_error());	
	$c = mysql_num_rows($q);		
	
	if($c>0){
	$msg .= '
	<h2>สต๊อกสินค้าวันที่ <span class="date" style="color:blue;">'. ThaiTimeConvert($datetime,0,0) .'</span></h2>
	<div class="clear"></div>
	<table cellspacing="0" cellpadding="0" border="1">
	<thead>
	  <tr>
		<td width="1" rowspan="2" align="center"><strong>ลำดับ</strong></td>
		<td width="174" rowspan="2" align="center"><strong>รายการสินค้า</strong></td>
		<td width="83" rowspan="2" align="center"><strong>ราคา/หน่วย</strong></td>
		<td width="52" rowspan="2" align="center"><strong>หน่วย</strong></td>
		<td width="69" rowspan="2" align="center"><strong>ยกมา</strong></td>
		<td width="67" rowspan="2" align="center"><strong>รับ</strong></td>
		<td width="58" rowspan="2" align="center"><strong>รวม</strong></td>
		<td colspan="2" align="center"><strong>จ่าย</strong></td>
		<td width="95" rowspan="2" align="center"><strong>จ่ายทั้งหมด</strong></td>
		<td width="67" rowspan="2" align="center"><strong>คงเหลือ</strong></td>
		<td width="74" rowspan="2" align="center"><strong>หมายเหตุ</strong></td>
	</tr>
	  <tr>
		<td width="70" align="center"><strong>จ่ายลูกค้า</strong></td>
		<td width="60" align="center"><strong>แถม</strong></td>
	</tr>
	</thead>
	<tbody>';
	$i=1; while($rs	 = mysql_fetch_assoc($q)){
	$msg .= '
	  <tr class="item-row">
		<td align="center">'.$i++.'</td>
		<td align="center">
			<div class="product-item">
				'.$rs['product_name'].'                    
			</div>
		</td>
		<td align="center">
			<div class="unit_price" data-price="'.$rs['price'].'">
				'.$rs['price'].'/'.$rs['name'].'
			</div>
		</td>
		<td align="center">
			<div class="unit_name">
				'.$rs['name'].'
			</div>
		</td>
		<td align="center">
			<div class="bring">
				'.$rs['bring'].'
			</div>
		</td>
		<td align="center">
			<div class="product-receive">
				'.$rs['receive'].'
			</div>
		</td>
		<td align="center">
			<div class="sum" field="sum">'.$rs['sum'].'</div>
		</td>
		<td align="center">
			<div class="product-pay-off">
				'.$rs['sale'].'
			</div>
		</td>
		<td align="center">
			<div class="product-bonus">
				'.$rs['bonus'].'
			</div>
		</td>
		<td align="center">
			<div class="all_sale" field="all_sale">'.$rs['all_sale'].'</div>
		</td>
		<td align="center">
			<div class="total" field="total">'.$rs['total'].'</div>
		</td>
		<td align="center">
			<div class="product-ann">
				'.(($rs['annotation']!="")?'<img src="css/images/info.gif" title="'.$rs['annotation'].'">"':'').'
			</div>
		</td>
	  </tr>';
	  }
  	$msg .= '        
	</tbody>
	</table>
	';
	echo $msg;
	}else{
		echo '<h1 style="margin-top:25px;text-align:center;font-size:40px;color:red;">วันนี้ไม่มีการทำรายการ</h1>';
	}
}


if($_POST['money_report']){
	
	$sumAllPrice = 0;
	$discount = 0;
	$sumCost = 0;
		
	if($_POST['result'] != ""){
		$date = $_POST['date2'];
		$y = substr($date,strrpos($date,'/')+1);
		$d = str_replace($y,((int)$y-543),$date);
		$datetime = strtotime($d);
		
	
		$m = (int)substr($date,0,strpos($date,'/'));
		$month = $FULL_MONTH[$m];
		
		$sql = 'SELECT * FROM stock WHERE stock_date = "'.date('dmY',$datetime).'"';
		$q = mysql_query($sql)or die(mysql_error());
		
		while($rs = mysql_fetch_assoc($q)){
			$sumAllPrice += ($rs['price'] * $rs['all_sale']);				
			$discount	 += ($rs['bonus'] * $rs['price']);				
			$sumCost	 += $rs['cost'] * $rs['all_sale'];
		}
	}
	
	echo $report ='
	<table border="1" cellspacing="2" cellpadding="2" width="300" align="center">
              <tr>
                <td colspan="2"><strong> สรุปยอดประจำเดือน <span class="month" style="color:blue;">'.$month.'</span></strong></td>
              </tr>
              <tr>
                <td>ต้นทุนรวม</td>
                <td align="right"><span style="color:red;">'. number_format($sumCost,2,'.',',') .'฿</span></td>
              </tr>
              <tr>
                <td>ขายได้รวม</td>
                <td align="right"><span style="color:red;">'. number_format($sumAllPrice,2,'.',',') .'฿</span></td>
              </tr>
              <tr>
                <td>ขายได้สุทธิ(ลบราคาของแถมแล้ว)</td>
                <td align="right"><span style="color:red;">'. number_format(($sumAllPrice-$discount),2,'.',',') .'฿</span></td>
              </tr>
              <tr>
                <td>กำไรสุทธิ(ขายได้สุทธิ - ต้นทุน)</td>
                <td align="right"><span style="color:red;">'. number_format(($sumAllPrice-$discount)-$sumCost,2,'.',',') .'฿</span></td>
              </tr>
            </table>';
}
?>

